package com.dao;

import com.domain.*;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.jdbc.SQL;
import org.springframework.stereotype.Repository;
import org.springframework.stereotype.Service;

import java.util.List;

@Repository
public interface UserDao {


    //注册
    @Options(useGeneratedKeys = true, keyProperty = "id", keyColumn = "id")
    @Insert("insert into user (icon, name, tel, password) " +
            "values(#{icon}, #{name}, #{tel}, #{password})")
    public void register(User user);


    //添加用户角色对应数据
    @Insert("insert into user_role (user_id, role_id) values(#{user_id}, #{role_id})")
    public void addRole(@Param("user_id") Integer user_id, @Param("role_id") Integer role_id);

    //添加内部用户部门数据
    @Insert("insert into user_department (user_id,department_id) values(#{user_id}, #{department_id})")
    public void addDepartment(@Param("user_id") Integer user_id, @Param("department_id") Integer department_id);

    //添加审批员级别数据
    @Insert("insert into user_rank (user_id,rank_name) values(#{user_id}, #{rank_name})")
    public void addRank(@Param("user_id")Integer user_id, @Param("rank_name")String rank_name);

    //添加维修类型
    @Insert("insert into unit_type (unit_id, type_id) values(#{unit_id}, #{type_id})")
    public void addMaintenanceType(@Param("unit_id")Integer unit_id, @Param("type_id")Integer type_id);

    //添加公司数据
    @Insert("insert into qualification (maintenance_unit_id, business_license, qualification_image, detail) values(#{id}, #{business_license}, #{qualification_image}, #{detail})")
    public void addCompany(User user);

    //添加维修方信息
    @Insert("insert into maintenance_unit (user_id, address) values (#{id}, #{address})")
    public void addUnit(User user);

    //添加内部人员单位信息
    @Insert("insert into user_insiderunit(user_id, insiderUnit_id) values(#{user_id}, #{insiderUnit_id})")
    public void addUserInsiderUnit(@Param("user_id") Integer user_id, @Param("insiderUnit_id") Integer insiderUnit_id);

    //添加单位
    @Insert("insert into insiderunit(insiderUnit_name) values (#{insiderUnit_name})")
    public void addInsiderUnit(String insiderUnit_name);

    //修改单位信息
    @Update("update insiderunit set insiderUnit_name = #{insiderUnit_name} where insiderUnit_id = #{insiderUnit_id}")
    public void updateInsiderUnit(@Param("insiderUnit_id") Integer insiderUnit_id, @Param("insiderUnit_name") String insiderUnit_name);

    //修改内部人员单位信息
    @Update("update user_insiderunit set insiderUnit_id = #{insiderUnit_id} where user_id = #{user_id}")
    public void updateUserInsiderUnit(Integer user_id, Integer insiderUnit_id);

    //查找内部人员单位信息
    @Select("select user.*, insiderunit.* from user, insiderunit, user_insiderunit " +
            "where user.id = #{user_id} and user_insiderunit.user_id = user.id and insiderunit.insiderUnit_id = user_insiderunit.insiderUnit_id")
    public InsiderUnit findInsiderUnitByUser(Integer user_id);

    //查找所有单位
    @Select("select insiderunit.* from insiderunit")
    public List<InsiderUnit> findAllInsiderUnit();


    //根据单位查询所有人员信息
    @Select("select user.*, role.role_id, role.role_name, insiderunit.insiderUnit_id, insiderunit.insiderUnit_name " +
            "from user, role, user_role, insiderunit, user_insiderunit " +
            "where user.id = user_insiderunit.user_id and " +
            "user.id = user_role.user_id and role.role_id = user_role.role_id and " +
            "insiderunit.insiderUnit_id = user_insiderunit.insiderUnit_id and " +
            "insiderunit.insiderUnit_id = #{insiderUnit_id}")
    public List<User> findAllUserByInsiderUnit(Integer insiderUnit_id);



    @Select("select user.*, user_rank.rank_name from user, user_rank where user.id = #{user_id} and user.id = user_rank.user_id")
    public User findEquipmentAdminRank(Integer user_id);

    //根据rank等级查询审批员
    @Select("select user.*, user_rank.* from user, user_rank " +
            "where user.id = user_rank.user_id and user_rank.rank_name = #{rank_name} and " +
            "user.account_state_id = 2")
    public List<User> findReviewerByRank(String rank_name);

    //根据id查询用户信息
    @Select("select user.*, role.role_id, role.role_name from user, role, user_role where " +
            "user.id = #{id} and " +
            "user.id = user_role.user_id and role.role_id = user_role.role_id")
    public User findById(Integer id);

    //登录
    @Select("select user.*, role.role_id, role.role_name from user, role, user_role where " +
            "user.tel = #{tel} and user.password = #{password} and " +
            "user.id = user_role.user_id and role.role_id = user_role.role_id")
    public List<User> login(@Param("tel") String tel, @Param("password") String password);


    //更新个人信息
    @Update("update user set icon = #{icon}, name = #{name}, tel = #{tel}, password = #{password} " +
            "where id = #{id}")
    public void updateUser(User user);

    //修改用户角色
    @Update("update user_role set role_id = #{role_id} where user_id = #{user_id}")
    public void updateRole(@Param("user_id") Integer user_id, @Param("role_id") Integer role_id);

    //修改用户部门
    @Update("update user_department set department_id = #{department_id} where user_id = #{user_id}")
    public void updateDepartment(@Param("user_id") Integer user_id, @Param("department_id") Integer department_id);

    //变更用户账号状态
    @Update("update user set account_state_id = #{account_state_id} where id = #{id}")
    public void updateAccount_state(@Param("id") Integer id, @Param("account_state_id") Integer account_state_id);





    //根据部门查找相应的审核员
    @Select("select user.*, role.role_id, role.role_name, department.department_id, department.department_name " +
            "from user, role, department, user_role, user_department " +
            "where user.id = user_role.user_id and role.role_id = user_role.role_id and " +
            "user.id = user_department.user_id and " +
            "user_department.department_id = #{department_id} and user_role.role_id = 3 and " +
            "department.department_id = user_department.department_id and " +
            "user.account_state_id = 2")
    public List<User> findApproverByDepartment(Integer department_id);

    //根据部门查找相应的审批员
    @Select("select user.*, role.role_id, role.role_name, user_rank.rank_name  " +
            "from user, role, user_role, user_rank " +
            "where user.id = user_role.user_id and role.role_id = user_role.role_id and " +
            "user_role.role_id = 4 and user.id = user_rank.user_id and " +
            "user.account_state_id = 2")
    public List<User> findReviewerByDepartment();

    //根据id查找内部人员信息
    @Select("select user.*, role.role_id, role.role_name, department.department_id, department.department_name " +
            "from user, role, department, user_role, user_department " +
            "where user.id = user_role.user_id and role.role_id = user_role.role_id and " +
            "user.id = user_department.user_id and department.department_id = user_department.department_id and " +
            "user.id = #{id}")
    public User findInsiderById(Integer id);


    //通过id查找公司信息
    @Select("select user.*, role.role_id, role.role_name, maintenance_unit.address, qualification.business_license, qualification.qualification_image, qualification.detail " +
            "from user, role, user_role, maintenance_unit, qualification " +
            "where user.id = #{id} and user.id = user_role.user_id and role.role_id = user_role.role_id and " +
            "maintenance_unit.user_id = user.id and qualification.maintenance_unit_id = user.id")
    public User findCompanyById(Integer id);

    //通过公司id查找营业执照
    @Select("select qualification.* from qualification where qualification.maintenance_unit_id = #{id}")
    public Qualification findQualification(Integer id);

    //通过公司id查找类型列表
    @Select("select unit_type.type_id from unit_type where unit_type.unit_id = #{id}")
    public List<Integer> findMaintenanceTypeByCompany(Integer id);

    //通过id查找维修师傅
    @Select("select user.*, role.role_id, role.role_name, maintenance_unit.address " +
            "from user, role, user_role, maintenance_unit " +
            "where user.id = #{id} and user.id = user_role.user_id and role.role_id = user_role.role_id and " +
            "maintenance_unit.user_id = user.id")
    public User findPersonById(Integer id);

    //通过维修单位id查询维修类型
    @Select("select maintenance_type.* " +
            "from maintenance_type, unit_type, maintenance_unit " +
            "where maintenance_type.id = unit_type.type_id and unit_type.unit_id = maintenance_unit.user_id and maintenance_unit.user_id = #{id}")
    public List<Maintenance_type> findMaintenanceType(Integer id);

    //根据id查询用户名称
    @Select("select name from user where id = #{id}")
    public String findNameById(Integer id);

    //删除单位
    @Delete("delete from insiderunit where insiderUnit_id = #{insiderUnit_id}")
    public void delInsiderUnit(Integer insiderUnit_id);

    //查询单位下所有人员信息
    @Select("select user.* from user, user_insiderunit where user.id = user_insiderunit.user_id and " +
            "user_insiderunit.insiderUnit_id = #{insiderUnit_id} and user.account_state_id = 2")
    public List<User> findUserByInsiderUnit(Integer insiderUnit_id);


    //根据维修类型查询所有维修方信息
    @Select("select user.*, user_role.role_id from user, user_role, unit_type " +
            "where user.account_state_id = 2 and user_role.role_id < 0 and user.id = user_role.user_id and " +
            "unit_type.type_id = #{type_id} and unit_type.unit_id = user.id and " +
            "user.account_state_id = 2")
    public List<User> findAllMaintenance_Person(Integer type_id);

    //根据账号状态显示所有账号信息
    @Select("select user.*, role.role_id, role.role_name from user, role, user_role where " +
            "user.account_state_id = #{account_state_id} and user.id = user_role.user_id and user_role.role_id = role.role_id")
    public List<User> findUserByState(Integer account_state_id);


    //删除用户信息
    @Delete("delete from user where id = #{user_id}")
    public void delUser(Integer user_id);

    @Delete("delete from user_role where user_id = #{user_id}")
    public void delUser_Role(Integer user_id);

    @Delete("delete from user_department where user_id = #{user_id}")
    public void delUser_Department(Integer user_id);

    @Delete("delete from unit_type where unit_id = #{unit_id}")
    public void delUnit_Type(Integer unit_id);

    @Delete("delete from qualification where maintenance_unit_id = #{unit_id}")
    public void delQualification(Integer unit_id);

    @Delete("delete from maintenance_unit where user_id = #{unit_id}")
    public void delMaintenance_unit(Integer unit_id);

    @Delete("delete from user_rank where user_id = #{user_id}")
    public void delRank(Integer user_id);

    @Delete("delete from user_insiderunit where user_id = #{user_id}")
    public void delUserInsiderUnit(Integer user_id);


    @Select("select user.*, role.role_id, role.role_name, user_rank.rank_name " +
            "from user, role, user_role, user_rank " +
            "where user.id = user_role.user_id and role.role_id = user_role.role_id and " +
            "user.id = user_rank.user_id and " +
            "user.id = #{id}")
    public User findReviewerById(Integer user_id);

    @Select("select user.*, role.role_id, role.role_name " +
            "from user, role, user_role " +
            "where user.id != #{user_id} and user.id = user_role.user_id and " +
            "user_role.role_id = role.role_id and user_role.role_id > 0 and user.account_state_id = 2")
    public List<User> findAllInsider(Integer user_id);

    //重置密码
    @Update("update user set password = '888888' where id = #{user_id}")
    public void resetPwd(Integer user_id);

    //根据部门查询人员
    @Select("select user.* from user, user_department where user.id = user_department.user_id and " +
            "user_department.department_id = #{department_id} and user.account_state_id = 2")
    public List<User> findInsiderByDepartment_id(Integer department_id);

    //查找所有设备管理员
    @Select("select user.*, role.role_id from user, user_role, role where " +
            "user.id = user_role.user_id and user_role.role_id = role.role_id and " +
            "user_role.role_id = 2 and user.account_state_id = 2")
    public List<User> findAllEquipmentAdmin();

    //判断账号是否存在
    @Select("select user.* from user where user.tel = #{tel}")
    public List<User> isUser(String tel);

    //根据手机查询正在使用的号码
    @Select("select user.* from user where user.tel = #{tel} and " +
            "(user.account_state_id = 2 or user.account_state_id = 1)")
    public List<User> findUsedUserByTel(String tel);

}
